package DBLayer;
import ModelLayer.*;
import java.sql.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;

public class DBOffer {

    private Connection con;

    // Constructor
    public DBOffer(){
        con = DbConnection1.getInstance().getDBcon();
    }

    // Method for getting all offers through the method miscWhere().
    public ArrayList<Offer> getAllOffers()
    {
        return miscWhere("");
    }

// Method for finding a specific offer through the offer id.
public Offer findOfferId(String idOffer){
    String wClause = "idOffer = '" + idOffer +"'";
    return singleWhere(wClause);
}


// Method for inserting a new Offer
public int insertOffer(Offer offer) throws Exception {
	int nextIdOffer = GetMax.getMaxId("Select max(idOffer) from offer");
        nextIdOffer = nextIdOffer + 1;
        System.out.println("next offerId = " + nextIdOffer);

        int rc = -1;
        String query = "INSERT INTO offer(idOffer, calculatedPrice, dateStart, dateEnd, acceptDeadline, " +
	"discount, statusOffer, sentDate, pricePerHour) " +
        	"VALUES('" +
        	nextIdOffer + "','" +
        	offer.getCalculatedPrice() + "','" +
        	offer.getDateStart() + "','" +
                offer.getDateEnd() + "','" +
                offer.getAcceptDeadline() + "','" +
                offer.getDiscount() + "','" +
                offer.getStatusOffer() + "','" +
                offer.getSentDate() + "','" +
                offer.getPricePerHour() + "')";

	System.out.println("insert : " + query);
        try {
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
       		rc = stmt.executeUpdate(query);
        	stmt.close();
        }
        catch (SQLException ex){
        	System.out.println("Offer not created");
        	throw new Exception ("Offer is not inserted correctly");
        }
        return rc;
    }



    // Method for updating an Offer
    public int updateOffer(Offer offer){
        Offer offerObj = offer;
        int rc=-1;

        String query = "UPDATE Offer SET " +
                "calculatedPrice ='" + offerObj.getCalculatedPrice() + "', " +
                "dateStart ='" + offerObj.getDateStart() + "', " +
                "dateEnd = '" + offerObj.getDateEnd() + "', " +
                "acceptDeadline ='" + offerObj.getAcceptDeadline() + "', " +
                "discount ='" + offerObj.getDiscount() + "', " +
                "statusOffer ='" + offerObj.getStatusOffer() + "', " +
                "sentDate ='" + offerObj.getSentDate() + "', " +
                "pricePerHour ='" + offerObj.getPricePerHour() + "'" +
                " WHERE idOffer = '" + offerObj.getIdOffer() + "'";
                System.out.println("Update query " + query);
        try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);

            stmt.close();
        }
        catch (Exception ex){
            System.out.println("Update exception in offer db: " + ex);
        }
        return rc;
    }


    public int deleteOffer(String idOffer){
        int rc = -1;

        String query = "DELETE FROM offer WHERE idOffer = '" +idOffer + "'";
        System.out.println(query);

        try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception ex){
            System.out.println("Delete exception in offer db: " +ex);
        }
        return(rc);
    }



    /**
     * Implementation of the private methods used in the above public methods is from here and downwards.
     */

    private ArrayList<Offer> miscWhere(String wClause)
    {
        ResultSet results;
        ArrayList<Offer> list = new ArrayList<Offer>();
        String query = buildQuery(wClause);

        // try to read the Offer from database
        try{
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while ( results.next()){
                Offer offerObj = new Offer();
                offerObj = buildOffer(results);
                list.add(offerObj);

            }
            stmt.close();
            // Associations has to be taken care of.
            /*
            if (retrieveAssociation)
            {
                for (Offer offerObj : list)
                {
                    String
                }
            }
            */
        }
        catch (Exception e)
        {
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return list;
    }

// SingleWhere method to select only one offer
private Offer singleWhere(String wClause)
{
	ResultSet results;
	Offer offerObj = new Offer();

    	String query = buildQuery(wClause);
    	System.out.println(query);

    	try{
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);

        	if ( results.next())
        	{
            		offerObj = buildOffer(results);
            		stmt.close();
        	}
        }
        catch (Exception e)
        {
        	System.out.println("Query exception" + e);
        }
        return offerObj;
}

// method to build the Query
private String buildQuery(String wClause)
{
	// This string query needs to be revised.
	String query = "Select idOffer, calculatedPrice, dateStart, dateEnd, acceptDeadline, " +
	"discount, statusOffer, sentDate, pricePerHour FROM offer";

	if (wClause.length() > 0)
	query=query+" WHERE "+ wClause;

	return query;
}


    // method to build an Offer object
    private Offer buildOffer(ResultSet results)
    {
        Offer offerObj = new Offer();
        try{
            offerObj.setIdOffer(results.getString("idOffer"));
            offerObj.setCalculatedPrice(results.getDouble("calculatedPrice"));
            offerObj.setDateStart(results.getString("dateStart"));
            offerObj.setDateEnd(results.getString("dateEnd"));
            offerObj.setAcceptDeadline(results.getString("acceptDeadline"));
            offerObj.setDiscount(results.getDouble("discount"));
            offerObj.setStatusOffer(results.getString("statusOffer"));
            offerObj.setSentDate(results.getString("sentDate"));
            offerObj.setPricePerHour(results.getDouble("pricePerHour"));
            //offerObj.setCustomer(new Customer(results.getString("idCustomer")));
            //offerObj.setPartialAssignmentList(new PartialAssignment(results.getString("idPartialAssignment")));
        }
        catch (Exception e){
            System.out.println("error in building the offer object");
        }
        return offerObj;
    }

}
